## -----------------------------
## 2020 COUNTIES
## -----------------------------

CDC_SVI_2020_county_dir <- here("Data", "CDC_Social_Vulnerability_Index", 
                                "2020_County_Data")

CDC_SVI_2020_county_files <- fs::dir_ls(CDC_SVI_2020_county_dir, 
                             regexp = "\\.csv$", recurse = TRUE)

CDC_SVI_2020_county <-
  map_dfr(CDC_SVI_2020_county_files, read_csv, show_col_types = FALSE) %>%
  clean_names() %>%
  mutate(
    year        = 2020,
    state_fips  = substr(stcnty, 1, 2),
    county_fips = substr(stcnty, 3, 5)
  ) %>%
  # CDC: treat -999 as missing, but ONLY for numeric columns
  mutate(across(where(is.numeric), ~na_if(.x, -999))) %>%
  dplyr::select(-st, -stcnty, -state, -county) %>%
  dplyr::select(st_abbr, state_fips, county_fips, everything())

## -----------------------------
## 2010 COUNTIES
## -----------------------------

CDC_SVI_2010_county_dir <- here("Data", "CDC_Social_Vulnerability_Index", 
                                "2010_County_Data")

CDC_SVI_2010_county_files <- fs::dir_ls(CDC_SVI_2010_county_dir, 
                             regexp = "\\.csv$", recurse = TRUE)

CDC_SVI_2010_county <-
  map_dfr(CDC_SVI_2010_county_files, read_csv, show_col_types = FALSE) %>%
  clean_names() %>%
  mutate(
    year        = 2010,
    state_fips  = substr(fips, 1, 2),
    county_fips = substr(fips, 3, 5)
  )  %>%
  # CDC: treat -999 as missing, but ONLY for numeric columns
  mutate(across(where(is.numeric), ~na_if(.x, -999))) %>%
  dplyr::select(-state, -fips) %>% 
  dplyr::select(st_abbr=st, state_fips, county_fips, everything())


## -----------------------------
## 2000 COUNTIES
## -----------------------------

CDC_SVI_2000_county_dir <- here("Data", "CDC_Social_Vulnerability_Index", 
                                "2000_County_Data")

CDC_SVI_2000_county_files <- fs::dir_ls(CDC_SVI_2000_county_dir,
                             regexp = "\\.csv$", recurse = TRUE)
CDC_SVI_2000_county <-
  map_dfr(CDC_SVI_2000_county_files, read_csv, show_col_types = FALSE) %>%
  clean_names() %>%
  mutate(
    year        = 2000,
    county_fips = as.character(cnty_fips),
    state_fips = as.character(state_fips),
  )   %>%
  # CDC: treat -999 as missing, but ONLY for numeric columns
  mutate(across(where(is.numeric), ~na_if(.x, -999))) %>%
  dplyr::select(-county, -stcofips, -cnty_fips) %>% 
  dplyr::select(st_abbr=state_abbr, state_fips, county_fips, everything())

#print(CDC_SVI_2020_county)
#print(CDC_SVI_2010_county)
#print(CDC_SVI_2000_county)

library(dplyr)
library(janitor)
library(gt)
library(scales)

# Helper to convert a 0–1 percentile to CDC-style quartile labels
quartile_lab <- function(x) {
  cut(
    x,
    breaks = c(-Inf, 0.25, 0.50, 0.75, Inf),
    labels = c(
      "0–0.25 (Least)",
      "0.25–0.50",
      "0.50–0.75",
      "0.75–1.00 (Most)"
    ),
    right = TRUE
  )
}

county_2020_ranked <-
  CDC_SVI_2020_county %>%
  clean_names() %>%
  filter(rpl_themes >= 0, rpl_themes <= 1) %>%  # <-- drop -999 and any stray
  mutate(
    geoid   = sprintf("%02s%03s", state_fips, county_fips),
    svi_nat = rpl_themes
  ) %>%
  group_by(st_abbr) %>%
  mutate(svi_state = percent_rank(svi_nat)) %>%  # re-rank within state
  ungroup() %>%
  mutate(
    nat_quart   = quartile_lab(svi_nat),
    state_quart = quartile_lab(svi_state)
  ) %>%
  dplyr::select(st_abbr, county_fips, location, svi_nat, svi_state, nat_quart, state_quart)

# Show the 25 highest national SVI counties
county_2020_ranked %>%
  dplyr::slice_max(order_by = svi_nat, n = 25, with_ties = FALSE) %>%
  gt::gt() %>%
  gt::fmt_number(columns = c(svi_nat, svi_state), decimals = 3) %>%
  gt::cols_label(
    st_abbr     = "State",
    county_fips = "County FIPS",
    location    = "County",
    svi_state   = "Overall SVI (state-based)",
    state_quart = "State quartile"
  ) %>%
  gt::tab_header(
    title    = gt::md("**Between-county Differences in Overall SVI (2020)**"),
    subtitle = gt::md("National vs. within-state percentiles & CDC-style quartiles")
  ) %>%
  gt::tab_source_note(
    gt::md("Notes: `RPL_THEMES` is the CDC national percentile rank (0–1). `svi_state` is re-ranked within state (0–1)..") 
  )

## -----------------------------
## 2020 TRACTS
## -----------------------------
CDC_SVI_2020_census_tract_dir <- here("Data","CDC_Social_Vulnerability_Index","2020_Census_Tract_Data")

CDC_SVI_2020_census_tract_files <- fs::dir_ls(CDC_SVI_2020_census_tract_dir, 
                                              regexp = "\\.csv$", recurse = TRUE)

CDC_SVI_2020_census_tract <-
  map_dfr(CDC_SVI_2020_census_tract_files, read_csv, show_col_types = FALSE) %>%
  clean_names() %>%
  mutate(
    year        = 2020,
    state_fips  = substr(stcnty, 1, 2),
    county_fips = substr(stcnty, 3, 5),
    state_fips  = sprintf("%02s", state_fips),
    county_fips = sprintf("%03s", county_fips),
    geoid       = if ("fips" %in% names(.)) sprintf("%011.0f", fips) else paste0(state_fips, county_fips, tract),
    st_abbr     = st_abbr %||% state,   # just in case
    state       = state %||% state_name # some files have both/one
  )  %>%
  # CDC: treat -999 as missing, but ONLY for numeric columns
  mutate(across(where(is.numeric), ~na_if(.x, -999))) %>% 
  dplyr::select(state, st_abbr, state_fips, county, county_fips, geoid, year, everything(), -st, -stcnty)

## -----------------------------
## 2010 TRACTS
## -----------------------------
CDC_SVI_2010_census_tract_dir <- here("Data","CDC_Social_Vulnerability_Index","2010_Census_Tract_Data")

CDC_SVI_2010_census_tract_files <- fs::dir_ls(CDC_SVI_2010_census_tract_dir, 
                                              regexp = "\\.csv$", recurse = TRUE)

CDC_SVI_2010_census_tract <-
  map_dfr(CDC_SVI_2010_census_tract_files, read_csv, show_col_types = FALSE) %>%
  clean_names() %>%
  mutate(
    year        = 2010,
    state_fips  = sprintf("%02s", state_fips),
    county_fips = sprintf("%03s", cnty_fips),
    geoid       = if ("fips" %in% names(.)) sprintf("%011.0f", fips) else paste0(state_fips, county_fips, tract),
    st_abbr     = state_abbr,
    state       = state_name
  )  %>%
  # CDC: treat -999 as missing, but ONLY for numeric columns
  mutate(across(where(is.numeric), ~na_if(.x, -999))) %>%
  dplyr::select(state, st_abbr, state_fips, county, county_fips, geoid, year, everything(), 
                -stcofips, -cnty_fips)

## -----------------------------
## 2000 TRACTS
## -----------------------------
CDC_SVI_2000_census_tract_dir <- here("Data","CDC_Social_Vulnerability_Index","2000_Census_Tract_Data")

CDC_SVI_2000_census_tract_files <- fs::dir_ls(CDC_SVI_2000_census_tract_dir,
                                              regexp = "\\.csv$", recurse = TRUE)

CDC_SVI_2000_census_tract <-
  map_dfr(CDC_SVI_2000_census_tract_files, read_csv, show_col_types = FALSE) %>%
  clean_names() %>%
  mutate(
    year        = 2000,
    state_fips  = sprintf("%02d", state_fips),
    county_fips = sprintf("%03s", cnty_fips),
    geoid       = paste0(state_fips, county_fips, tract),
    st_abbr     = state_abbr,
    state       = state_name,
    county      = county
  ) %>%
  dplyr::select(state, st_abbr, state_fips, county, county_fips, geoid, year, everything(),
                -stcofips, -cnty_fips)

#print(CDC_SVI_2020_census_tract)
#print(CDC_SVI_2010_census_tract)
#print(CDC_SVI_2000_census_tract)

tract_2020_ranked <-
  CDC_SVI_2020_census_tract %>%
  clean_names() %>%
  # keep only valid overall SVI percentiles
  filter(!is.na(rpl_themes), dplyr::between(rpl_themes, 0, 1)) %>%
  mutate(
    st_abbr = coalesce(st_abbr, state),
    svi_nat = rpl_themes                     # national percentile (given by CDC)
    # geoid already exists from your read-in code, so we don't touch it
  ) %>%
  group_by(st_abbr) %>%
  mutate(svi_state = percent_rank(svi_nat)) %>%
  ungroup() %>%
  mutate(
    nat_quart   = quartile_lab(svi_nat),
    state_quart = quartile_lab(svi_state)
  ) %>%
  dplyr::select(st_abbr, county, location,
         svi_nat, svi_state, nat_quart, state_quart)

# Show the 25 most vulnerable tracts nationally
tract_2020_ranked %>%
  arrange(desc(svi_nat)) %>%
  slice_head(n = 25) %>%
  gt() %>%
  fmt_number(columns = c(svi_nat, svi_state), decimals = 3) %>%
  cols_label(
    st_abbr     = "State",
    county      = "County",
    location    = "Location",
    svi_nat     = "Overall SVI (national)",
    svi_state   = "Overall SVI (state-based)",
    nat_quart   = "Nat. quartile",
    state_quart = "State quartile"
  ) %>%
  tab_header(
    title    = md("**Between-tract Differences in Overall SVI (2020)**"),
    subtitle = md("National vs. within-state percentiles & CDC-style quartiles")
  ) %>%
  tab_source_note(
    md("Notes: `RPL_THEMES` is the CDC national percentile rank (0–1). `svi_state` is re-ranked within state (0–1).")
  )